package com.demo.las.repository;

import com.demo.las.entity.StudyPlan;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

@Repository
public interface StudyPlanRepository extends JpaRepository<StudyPlan, Long> {
    
    List<StudyPlan> findByUserIdOrderByCreateTimeDesc(Long userId);
    
    List<StudyPlan> findByUserIdAndStatus(Long userId, StudyPlan.Status status);
    
    List<StudyPlan> findByUserIdAndPriority(Long userId, StudyPlan.Priority priority);
    
    List<StudyPlan> findByUserIdAndSubject(Long userId, String subject);
    
    @Query("SELECT sp FROM StudyPlan sp WHERE sp.userId = :userId AND sp.status = 'ACTIVE' AND sp.planEndDate >= CURRENT_DATE ORDER BY sp.priority DESC, sp.planEndDate ASC")
    List<StudyPlan> findActivePlansByUser(@Param("userId") Long userId);
    
    @Query("SELECT sp FROM StudyPlan sp WHERE sp.userId = :userId AND sp.status = 'ACTIVE' AND sp.planEndDate < CURRENT_DATE")
    List<StudyPlan> findOverduePlansByUser(@Param("userId") Long userId);
    
    @Query("SELECT sp FROM StudyPlan sp WHERE sp.userId = :userId AND sp.status = 'ACTIVE' AND sp.planEndDate BETWEEN CURRENT_DATE AND :endDate")
    List<StudyPlan> findDueSoonPlansByUser(@Param("userId") Long userId, @Param("endDate") LocalDate endDate);
    
    @Query("SELECT SUM(sp.targetHours) FROM StudyPlan sp WHERE sp.userId = :userId AND sp.status = 'ACTIVE'")
    BigDecimal getTotalTargetHoursByUser(@Param("userId") Long userId);
    
    @Query("SELECT SUM(sp.completedHours) FROM StudyPlan sp WHERE sp.userId = :userId AND sp.status = 'ACTIVE'")
    BigDecimal getTotalCompletedHoursByUser(@Param("userId") Long userId);
    
    @Query("SELECT sp.subject, SUM(sp.targetHours) as target, SUM(sp.completedHours) as completed FROM StudyPlan sp WHERE sp.userId = :userId AND sp.status = 'ACTIVE' GROUP BY sp.subject")
    List<Object[]> getPlanProgressBySubject(@Param("userId") Long userId);
    
    @Query("SELECT sp.priority, COUNT(*) as count FROM StudyPlan sp WHERE sp.userId = :userId AND sp.status = 'ACTIVE' GROUP BY sp.priority")
    List<Object[]> getPlanCountByPriority(@Param("userId") Long userId);
    
    @Query("SELECT sp FROM StudyPlan sp WHERE sp.userId = :userId AND sp.planStartDate <= :date AND sp.planEndDate >= :date")
    List<StudyPlan> findPlansActiveOnDate(@Param("userId") Long userId, @Param("date") LocalDate date);
}




